package com.wontube.smr.dao.providers;

import static org.apache.ibatis.jdbc.SqlBuilder.BEGIN;
import static org.apache.ibatis.jdbc.SqlBuilder.FROM;
import static org.apache.ibatis.jdbc.SqlBuilder.INNER_JOIN;
import static org.apache.ibatis.jdbc.SqlBuilder.LEFT_OUTER_JOIN;
import static org.apache.ibatis.jdbc.SqlBuilder.ORDER_BY;
import static org.apache.ibatis.jdbc.SqlBuilder.SELECT;
import static org.apache.ibatis.jdbc.SqlBuilder.SELECT_DISTINCT;
import static org.apache.ibatis.jdbc.SqlBuilder.SQL;

import java.util.Map;

import com.wontube.core.dao.mybatis.QueryExample;
import com.wontube.core.dao.mybatis.provider.SqlProvider;


/**
 * @author yuechao
 *
 */
public final class TVEpisodeSqlProvider extends SqlProvider {
	public String countWith(QueryExample example) {
        BEGIN();
    
        SELECT("count(*)");
        FROM("tv_episode");
        INNER_JOIN("tv_show on tv_episode.show_id = tv_show.id");
        
        applyWhere(example, false);
        
        return SQL();
	}
	
	public String selectWith(QueryExample example) {
        BEGIN();
        if (example != null && example.isDistinct()) {
            SELECT_DISTINCT("tv_episode.id");
        } else {
            SELECT("tv_episode.id");
        }
        
        SELECT("tv_episode.show_id as showId, tv_episode.episode_name as episodeName, tv_episode.introduction");
        SELECT("tv_episode.thumbnail, tv_episode.play_cnt as playCnt, tv_episode.season");
        SELECT("tv_episode.part, tv_episode.url, tv_episode.definition, tv_episode.website");
        SELECT("tv_episode.version, tv_episode.score, tv_episode.air_date as airDate, tv_episode.duration");
        SELECT("tv_show.genre, tv_show.name as showName");
        SELECT("tv_show.season_count as seasonCnt, tv_show.episode_cnt as episodeCnt");
        
        FROM("tv_episode");
        INNER_JOIN("tv_show on tv_episode.show_id = tv_show.id");

        applyWhere(example, false);
        
        if (example != null && example.getOrderByClause() != null) {
            ORDER_BY(example.getOrderByClause());
        }
        
        return SQL();
	}
	
	public String countWithPopular(QueryExample example) {
        BEGIN();
    
        SELECT("count(*)");
        FROM("tv_episode");
        INNER_JOIN("tv_show on tv_episode.show_id = tv_show.id");
        LEFT_OUTER_JOIN("popular_episode on tv_episode.id = popular_episode.episode_id");
        
        applyWhere(example, false);
        
        return SQL();
	}
	
	public String selectWithPopular(QueryExample example) {
        BEGIN();
        if (example != null && example.isDistinct()) {
            SELECT_DISTINCT("tv_episode.id");
        } else {
            SELECT("tv_episode.id");
        }
        
        SELECT("tv_episode.show_id as showId, tv_episode.episode_name as episodeName, tv_episode.introduction");
        SELECT("tv_episode.thumbnail, tv_episode.play_cnt as playCnt, tv_episode.season");
        SELECT("tv_episode.part, tv_episode.url, tv_episode.definition, tv_episode.website");
        SELECT("tv_episode.version, tv_episode.score, tv_episode.air_date as airDate, tv_episode.duration");
        SELECT("tv_show.genre, tv_show.name as showName");
        SELECT("tv_show.season_count as seasonCnt, tv_show.episode_cnt as episodeCnt");
        FROM("tv_episode");
        INNER_JOIN("tv_show on tv_episode.show_id = tv_show.id");
        LEFT_OUTER_JOIN("popular_episode on tv_episode.id = popular_episode.episode_id");
        
        applyWhere(example, false);
        
        if (example != null && example.getOrderByClause() != null) {
            ORDER_BY(example.getOrderByClause());
        }
        
        return SQL();
	}
	
	public String countWithPopularByGenre(Map<String, Object> map) {
		QueryExample example = (QueryExample) map.get("example");
		
        BEGIN();
    
        SELECT("count(*)");
        FROM("tv_episode");
        INNER_JOIN("tv_show on tv_episode.show_id = tv_show.id");
        LEFT_OUTER_JOIN("popular_episode on tv_episode.id = popular_episode.episode_id");
        INNER_JOIN("tvshow_genre on tvshow_genre.show_id = tv_show.id");
        
		String genreName = (String)map.get("genreName");
		applyInCondition("tvshow_genre.genre_name", genreName);

        applyWhere(example, true);
        
        return SQL();
	}
	
	public String selectWithPopularByGenre(Map<String, Object> map) {
		QueryExample example = (QueryExample) map.get("example");
		
        BEGIN();
        if (example != null && example.isDistinct()) {
            SELECT_DISTINCT("tv_episode.id");
        } else {
            SELECT("tv_episode.id");
        }
        
        SELECT("tv_episode.show_id as showId, tv_episode.episode_name as episodeName, tv_episode.introduction");
        SELECT("tv_episode.thumbnail, tv_episode.play_cnt as playCnt, tv_episode.season");
        SELECT("tv_episode.part, tv_episode.url, tv_episode.definition, tv_episode.website");
        SELECT("tv_episode.version, tv_episode.score, tv_episode.air_date as airDate, tv_episode.duration");
        SELECT("tv_show.genre, tv_show.name as showName");
        SELECT("tv_show.season_count as seasonCnt, tv_show.episode_cnt as episodeCnt");
        FROM("tv_episode");

        INNER_JOIN("tv_show on tv_episode.show_id = tv_show.id");
        LEFT_OUTER_JOIN("popular_episode on tv_episode.id = popular_episode.episode_id");
        INNER_JOIN("tvshow_genre on tvshow_genre.show_id = tv_show.id");
        
		String genreName = (String)map.get("genreName");
		applyInCondition("tvshow_genre.genre_name", genreName);
        
        applyWhere(example, true);
        
        if (example != null && example.getOrderByClause() != null) {
            ORDER_BY(example.getOrderByClause());
        }
        
        return SQL();
	}
	
	public String countWithRecent(QueryExample example) {
        BEGIN();
    
        SELECT("count(*)");
        FROM("tv_episode");
        INNER_JOIN("tv_show on tv_episode.show_id = tv_show.id");
        LEFT_OUTER_JOIN("recent_episode on tv_episode.id = recent_episode.episode_id");
        
        applyWhere(example, false);
        
        return SQL();
	}
	
	public String selectWithRecent(QueryExample example) {
        BEGIN();
        if (example != null && example.isDistinct()) {
            SELECT_DISTINCT("tv_episode.id");
        } else {
            SELECT("tv_episode.id");
        }

        SELECT("tv_episode.show_id as showId, tv_episode.episode_name as episodeName, tv_episode.introduction");
        SELECT("tv_episode.thumbnail, tv_episode.play_cnt as playCnt, tv_episode.season");
        SELECT("tv_episode.part, tv_episode.url, tv_episode.definition, tv_episode.website");
        SELECT("tv_episode.version, tv_episode.score, tv_episode.air_date as airDate, tv_episode.duration");
        SELECT("tv_show.genre, tv_show.name as showName");
        SELECT("tv_show.season_count as seasonCnt, tv_show.episode_cnt as episodeCnt");
        
        FROM("tv_episode");
        INNER_JOIN("tv_show on tv_episode.show_id = tv_show.id");
        LEFT_OUTER_JOIN("recent_episode on tv_episode.id = recent_episode.episode_id");
        
        applyWhere(example, false);
        
        if (example != null && example.getOrderByClause() != null) {
            ORDER_BY(example.getOrderByClause());
        }
        
        return SQL();
	}
	
	public String countWithRecentByGenre(Map<String, Object> map) {
		QueryExample example = (QueryExample) map.get("example");
		
        BEGIN();
    
        SELECT("count(*)");
        
        FROM("tv_episode");
        INNER_JOIN("tv_show on tv_episode.show_id = tv_show.id");
        LEFT_OUTER_JOIN("recent_episode on tv_episode.id = recent_episode.episode_id");
        INNER_JOIN("tvshow_genre on tvshow_genre.show_id = tv_show.id");
        
		String genreName = (String)map.get("genreName");
		applyInCondition("tvshow_genre.genre_name", genreName);
		
        applyWhere(example, true);
        
        return SQL();
	}
	
	public String selectWithRecentByGenre(Map<String, Object> map) {
		QueryExample example = (QueryExample) map.get("example");
		
        BEGIN();
        if (example != null && example.isDistinct()) {
            SELECT_DISTINCT("tv_episode.id");
        } else {
            SELECT("tv_episode.id");
        }
        
        SELECT("tv_episode.show_id as showId, tv_episode.episode_name as episodeName, tv_episode.introduction");
        SELECT("tv_episode.thumbnail, tv_episode.play_cnt as playCnt, tv_episode.season");
        SELECT("tv_episode.part, tv_episode.url, tv_episode.definition, tv_episode.website");
        SELECT("tv_episode.version, tv_episode.score, tv_episode.air_date as airDate, tv_episode.duration");
        SELECT("tv_show.genre, tv_show.name as showName");
        SELECT("tv_show.season_count as seasonCnt, tv_show.episode_cnt as episodeCnt");
        
        FROM("tv_episode");
        INNER_JOIN("tv_show on tv_episode.show_id = tv_show.id");
        LEFT_OUTER_JOIN("recent_episode on tv_episode.id = recent_episode.episode_id");
        INNER_JOIN("tvshow_genre on tvshow_genre.show_id = tv_show.id");
        
		String genreName = (String)map.get("genreName");
		applyInCondition("tvshow_genre.genre_name", genreName);
        
        applyWhere(example, true);
        
        if (example != null && example.getOrderByClause() != null) {
            ORDER_BY(example.getOrderByClause());
        }
        
        return SQL();
	}
}
